﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Collections;

/// <summary>
///ShopDao 的摘要说明
/// </summary>
public class ShopDao
{
    DBMM dbm = new DBMM();
    DataTable dt = null;
    string sql = null;
    ShopVo sv = new ShopVo();

    //添加新店面
    public Exception addShop(ShopVo sv)
    {
        sql = "insert into Shop values(@Sname,@Fid,@Tid,@Sintroduce,@Scontent,@Sjob,@Saddress,@Stel,@Slogo,@Spic,@Shot,0,@Sstate,@Uid,@Slicense,@Sidcard)";
        SqlParameter[] sp = new SqlParameter[] { new SqlParameter("@Sname", sv.Sname), 
                                                 new SqlParameter("@Fid", sv.Fid), 
                                                 new SqlParameter("@Tid", sv.Tid), 
                                                 new SqlParameter("@Sintroduce", sv.Sintroduce), 
                                                 new SqlParameter("@Scontent", sv.Scontent),
                                                 new SqlParameter("@Sjob", sv.Sjob),
                                                 new SqlParameter("@Saddress", sv.Saddress),
                                                 new SqlParameter("@Stel", sv.Stel), 
                                                 new SqlParameter("@Slogo", sv.Slogo), 
                                                 new SqlParameter("@Spic", sv.Spic),
                                                 new SqlParameter("@Shot", sv.Shot),
                                                 new SqlParameter("@Sstate", sv.Sstate),
                                                 new SqlParameter("@Uid", sv.Uid),
                                                 new SqlParameter("@Slicense", sv.Slicense),
                                                 new SqlParameter("@Sidcard", sv.Sidcard)};
        Exception ex = null;
        ex = dbm.Execute(sql, sp);
        return ex;
    }

    //查找首页最新店面信息
    public DataTable getShop(string count)
    {
        sql = "select top " + count + " * from Shop left join Field on Shop.Fid=Field.Fid left join Type on Shop.Tid=Type.Tid where Sstate=0 order by Sid desc";
        dt = new DataTable();
        dt = dbm.GetTable(sql);
        return dt;
    }

    //查找所有店面信息
    public DataTable getAllShop()
    {
        sql = "select  * from Shop left join Field on Shop.Fid=Field.Fid left join Type on Shop.Tid=Type.Tid order by Sid desc";
        dt = new DataTable();
        dt = dbm.GetTable(sql);
        return dt;
    }

    //随机显示首页店面信息
    public DataTable getIndexShop(int count)
    {
        sql = "select top 1 Sid from Shop order by Sid desc";
        dt = new DataTable();
        dt = dbm.GetTable(sql);
        if (dt.Rows.Count> 0)
        {
            int maxvalue = Convert.ToInt32(dt.Rows[0][0]);
            if (maxvalue > count)
            {
                string SidList = "";
                Random random = new Random();
                ArrayList list = new ArrayList();
                for (int i = 0; i < count; i++)
                {
                    bool flag = true;
                    int id = random.Next(1, maxvalue + 1);
                    while (flag)
                    {
                        if (list.Contains(id.ToString()))
                        {
                            id = random.Next(1, maxvalue + 1);
                            flag = true;
                        }
                        else
                        {
                            list.Add(id.ToString());
                            flag = false;
                        }
                    }
                    if (i == 0)
                    {
                        SidList = id.ToString();
                    }
                    else
                    {
                        SidList = SidList + "," + id.ToString();
                    }
                }
                sql = "select * from Shop left join Field on Shop.Fid=Field.Fid left join Type on Shop.Tid=Type.Tid where Sstate=0 and Sid in (" + SidList + ")";
            }
            else
            {
                sql = "select * from Shop order by Sid desc";
            }
            dt = new DataTable();
            dt = dbm.GetTable(sql);
            return dt;
        }
        else
        {
            return null;
        }
    }

    //查找相关店面信息
    public DataTable getShop(string fid, string tid, string name, string count)
    {
        if (count == "0")
        {
            if (fid == "0")
            {
                sql = "select * from Shop left join Field on Shop.Fid=Field.Fid left join Type on Shop.Tid=Type.Tid where Sstate=0 and Sname like @Sname order by Sup desc";
            }
            else
            {
                if (tid == "0")
                {
                    sql = "select * from Shop left join Field on Shop.Fid=Field.Fid left join Type on Shop.Tid=Type.Tid where Sstate=0 and Sname like @Sname and Shop.Fid=@Fid order by Sup desc";
                }
                else
                {
                    sql = "select * from Shop left join Field on Shop.Fid=Field.Fid left join Type on Shop.Tid=Type.Tid where Sstate=0 and Sname like @Sname and Shop.Fid=@Fid and Shop.Tid=@Tid order by Sup desc";
                }
            }
        }
        else
        {
            if (tid == "0")
            {
                sql = "select top " + count + " * from Shop left join Field on Shop.Fid=Field.Fid left join Type on Shop.Tid=Type.Tid where Sstate=0 and Sname like @Sname and Shop.Fid=@Fid order by Sup desc";
            }
            else
            {
                sql = "select top " + count + " * from Shop left join Field on Shop.Fid=Field.Fid left join Type on Shop.Tid=Type.Tid where Sstate=0 and Sname like @Sname and Shop.Fid=@Fid and Shop.Tid=@Tid order by Sup desc";
            }
        }
        SqlParameter[] sp = new SqlParameter[] { new SqlParameter("@Sname", "%" + name + "%"),
                                                 new SqlParameter("@Fid", fid),
                                                 new SqlParameter("@Tid", tid)};
        dt = new DataTable();
        dt = dbm.GetTable(sql, sp);
        return dt;
    }

    //查找相同类型店面信息
    public DataTable getShop(string sid,string fid, string tid, string name,string count)
    {
        if (tid == "0")
        {
            sql = "select top " + count + " * from Shop left join Field on Shop.Fid=Field.Fid left join Type on Shop.Tid=Type.Tid where Sstate=0 and Sname like @Sname and Shop.Fid=@Fid and Sid<>@Sid order by Sup desc";
        }
        else
        {
            sql = "select top " + count + " * from Shop left join Field on Shop.Fid=Field.Fid left join Type on Shop.Tid=Type.Tid where Sstate=0 and Sname like @Sname and Shop.Fid=@Fid and Shop.Tid=@Tid and Sid<>@Sid order by Sup desc";
        }
        SqlParameter[] sp = new SqlParameter[] { new SqlParameter("@Sid", sid),
                                                 new SqlParameter("@Sname", "%" + name + "%"),
                                                 new SqlParameter("@Fid", fid),
                                                 new SqlParameter("@Tid", tid)};
        dt = new DataTable();
        dt = dbm.GetTable(sql, sp);
        return dt;
    }

    //获得单个店面信息
    public ShopVo getSingleShop(string id)
    {
        sql = "select * from Shop where Sid=@Sid";
        SqlParameter[] sp = new SqlParameter[] { new SqlParameter("@Sid", id) };
        dt = new DataTable();
        dt = dbm.GetTable(sql, sp);
        sv = new ShopVo();
        sv.Sid = Convert.ToInt32(dt.Rows[0][0]);
        sv.Sname = dt.Rows[0][1].ToString();
        sv.Fid = Convert.ToInt32(dt.Rows[0][2]);
        sv.Tid = Convert.ToInt32(dt.Rows[0][3]);
        sv.Sintroduce = dt.Rows[0][4].ToString();
        sv.Scontent = dt.Rows[0][5].ToString();
        sv.Sjob = dt.Rows[0][6].ToString();
        sv.Saddress = dt.Rows[0][7].ToString();
        sv.Stel = dt.Rows[0][8].ToString();
        sv.Slogo = dt.Rows[0][9].ToString();
        sv.Spic = dt.Rows[0][10].ToString();
        sv.Shot = Convert.ToInt32(dt.Rows[0][11]);
        sv.Sup = Convert.ToInt32(dt.Rows[0][12]);
        sv.Sstate = Convert.ToInt32(dt.Rows[0][13]);
        sv.Uid = Convert.ToInt32(dt.Rows[0][14]);
        sv.Slicense = dt.Rows[0][15].ToString();
        sv.Sidcard = dt.Rows[0][16].ToString();
        return sv;
    }


    //修改相关店面信息
    public Exception updateSingleShop(ShopVo sv)
    {
        sql = "update Shop set Sname=@Sname,Fid=@Fid,Tid=@Tid,Sintroduce=@Sintroduce,Scontent=@Scontent,Sjob=@Sjob,Saddress=@Saddress,Stel=@Stel,Slogo=@Slogo,Spic=@Spic,Shot=@Shot,Slicense=@Slicense,Sidcard=@Sidcard where Sid=@Sid";
        SqlParameter[] sp = new SqlParameter[] { new SqlParameter("@Sname", sv.Sname), 
                                                 new SqlParameter("@Fid", sv.Fid), 
                                                 new SqlParameter("@Tid", sv.Tid), 
                                                 new SqlParameter("@Sintroduce", sv.Sintroduce), 
                                                 new SqlParameter("@Scontent", sv.Scontent),
                                                 new SqlParameter("@Sjob", sv.Sjob),
                                                 new SqlParameter("@Saddress", sv.Saddress),
                                                 new SqlParameter("@Stel", sv.Stel),
                                                 new SqlParameter("@Slogo", sv.Slogo),
                                                 new SqlParameter("@Spic", sv.Spic),
                                                 new SqlParameter("@Shot", sv.Shot),
                                                 new SqlParameter("@Slicense", sv.Slicense),
                                                 new SqlParameter("@Sidcard", sv.Sidcard),
                                                 new SqlParameter("@Sid", sv.Sid)};
        Exception ex = null;
        ex = dbm.Execute(sql, sp);
        return ex;
    }

    //删除单个店面
    public Exception delShop(string id)
    {
        sql = "delete Shop where Sid=@Sid";
        SqlParameter[] sp = new SqlParameter[] { new SqlParameter("@Sid", id) };
        Exception ex = null;
        ex = dbm.Execute(sql, sp);
        return ex;
    }

    //查找所有领域
    public DataTable getField()
    {
        sql = "select * from Field";
        dt = new DataTable();
        dt = dbm.GetTable(sql);
        return dt;
    }

    //查找相应类型版块
    public DataTable getType(string fid)
    {
        sql = "select * from Type where Fid=@Fid";
        SqlParameter[] sp = new SqlParameter[] { new SqlParameter("@Fid", fid) };
        dt = new DataTable();
        dt = dbm.GetTable(sql, sp);
        return dt;
    }

    //查找领域名称
    public string getFieldName(string fid)
    {
        sql = "select Fname from Field where Fid=@Fid";
        SqlParameter[] sp = new SqlParameter[] { new SqlParameter("@Fid", fid) };
        dt = new DataTable();
        dt = dbm.GetTable(sql, sp);
        string name = dt.Rows[0][0].ToString();
        return name;
    }

    //查找类型名称
    public string getTypeName(string tid)
    {
        sql = "select Tname from Type where Tid=@Tid";
        SqlParameter[] sp = new SqlParameter[] { new SqlParameter("@Tid", tid) };
        dt = new DataTable();
        dt = dbm.GetTable(sql, sp);
        string name = dt.Rows[0][0].ToString();
        return name;
    }

    //增加支持数
    public Exception ShopAddUp(string id)
    {
        sql = "update Shop set Sup=Sup+1 where Sid=@Sid";
        SqlParameter[] sp = new SqlParameter[] { new SqlParameter("@Sid", id) };
        Exception ex = null;
        ex = dbm.Execute(sql, sp);
        return ex;
    }

    //检查用户名是否可用
    public string CheckShopName(string name)
    {
        sql = "select * from Shop where Sname=@Sname";
        SqlParameter[] sp = new SqlParameter[] { new SqlParameter("@Sname", name) };
        dt = new DataTable();
        dt = dbm.GetTable(sql, sp);
        if (dt.Rows.Count > 0)
        {
            string str = "对不起，店名已存在！";
            return str;
        }
        else
        {
            string str = "恭喜，店名可用！";
            return str;
        }
    }

    //通过审核
    public Exception UpdateShopState(string id)
    {
        sql = "update Shop set Sstate=0 where Sid=@Sid";
        SqlParameter[] sp = new SqlParameter[] { new SqlParameter("@Sid", id) };
        Exception ex = null;
        ex = dbm.Execute(sql, sp);
        return ex;
    }

    //获得新添加店面id
    public int getNewShopId(string name)
    {
        sql = "select Sid from Shop where Sname=@Sname";
        SqlParameter[] sp = new SqlParameter[] { new SqlParameter("@Sname", name) };
        dt = new DataTable();
        dt = dbm.GetTable(sql, sp);
        return Convert.ToInt32(dt.Rows[0][0]);
    }

    //查找相关状态店面信息
    public DataTable getStateShop(string state)
    {
        if (state=="2")
        {
            sql = "select * from Shop left join Field on Shop.Fid=Field.Fid left join Type on Shop.Tid=Type.Tid order by Sid desc";
        }
        else
        {
            sql = "select * from Shop left join Field on Shop.Fid=Field.Fid left join Type on Shop.Tid=Type.Tid where Sstate=@Sstate order by Sid desc";
        }
        SqlParameter[] sp = new SqlParameter[] { new SqlParameter("@Sstate", state) };
        dt = new DataTable();
        dt = dbm.GetTable(sql, sp);
        return dt;
    }

    //获得商家单个店面信息
    public ShopVo getUserShop(string uid)
    {
        sql = "select * from Shop where Uid=@Uid";
        SqlParameter[] sp = new SqlParameter[] { new SqlParameter("@Uid", uid) };
        dt = new DataTable();
        dt = dbm.GetTable(sql, sp);
        sv = new ShopVo();
        sv.Sid = Convert.ToInt32(dt.Rows[0][0]);
        sv.Sname = dt.Rows[0][1].ToString();
        sv.Fid = Convert.ToInt32(dt.Rows[0][2]);
        sv.Tid = Convert.ToInt32(dt.Rows[0][3]);
        sv.Sintroduce = dt.Rows[0][4].ToString();
        sv.Scontent = dt.Rows[0][5].ToString();
        sv.Sjob = dt.Rows[0][6].ToString();
        sv.Saddress = dt.Rows[0][7].ToString();
        sv.Stel = dt.Rows[0][8].ToString();
        sv.Slogo = dt.Rows[0][9].ToString();
        sv.Spic = dt.Rows[0][10].ToString();
        sv.Shot = Convert.ToInt32(dt.Rows[0][11]);
        sv.Sup = Convert.ToInt32(dt.Rows[0][12]);
        sv.Sstate = Convert.ToInt32(dt.Rows[0][13]);
        sv.Uid = Convert.ToInt32(dt.Rows[0][14]);
        sv.Slicense = dt.Rows[0][15].ToString();
        sv.Sidcard = dt.Rows[0][16].ToString();
        return sv;
    }
}
